#!/use/bin/python
# coding=utf-8
# 主动服务 开机率 analysis_startup_rate
import datetime
import pymysql

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# 记录 数据分析-主动服务-开机率对比 analysis_startup_rate  不区分人群id
def insertStartupRate(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_startup_rate 表是否已经存在当天 开机率数据
        selectCountSql = """
                    select count(1) num from analysis_startup_rate t where 1=1 and t.ref_date =  '%s' and crowd_id = 1
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日推送的设备数量,人群圈中的设备且(进去了生活助手设置页面或收到并看到早间提醒晚上的弹窗)
        selectNumSql = """
                    select count(DISTINCT(t.active_id)) from log_device_crowd t where t.see_flag =1 or t.setting_flag =1
                """;
        cur.execute(selectNumSql)
        resultNum = cur.fetchone()
        #计算 当日开机设备量
        selectStartupNumSql = """
                    select count(DISTINCT(t.active_id)) from log_sys_poweron t  where  DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and 
    t.active_id in(select tt.active_id from log_device_crowd tt where (tt.see_flag =1 or tt.setting_flag =1) and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s')
                """ % (date,date)
        cur.execute(selectStartupNumSql)
        resultStartupNum = cur.fetchone()
        # 计算开机率
        startupRate = 0
        startupNum = resultStartupNum[0]
        pushNum = resultNum[0]
        if pushNum!=0:
            startupRate = round(startupNum/pushNum,4)*100

        if countNum == 0:
            print ("%s日新增推送的设备数量为%s,开机设备量为%s" % (date,pushNum,startupNum))
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_startup_rate(startup_rate,startup_num,push_num,ref_date,crowd_id) 
                value(%s,%s,%s,'%s',%s)""" % (startupRate,startupNum,pushNum,date,1)
            cur.execute(insertSql)
        else:
            print ("%s日更新推送的设备数量为%s,开机设备量为%s" % (date,pushNum,startupNum))
            # 更新早间服务可见设备量
            updateSql = """
                update analysis_startup_rate set startup_rate = %s ,startup_num = %s,push_num = %s ,update_time = now()  where
                    ref_date = '%s' and crowd_id = 1
            """ % (startupRate,startupNum,pushNum,date);
            cur.execute(updateSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 数据分析-主动服务-开机率对比 analysis_startup_rate 区分人群id
def insertStartupRateCrowdId(date):
    try:
        conn, cur = getDataConnection()

        # 获取当日推送的设备数量,人群圈中的设备且(进去了生活助手设置页面或收到并看到早间提醒晚上的弹窗) 使用人群id进行分组
        selectNumSql = """
                    select count(DISTINCT(t.active_id)) num,crowd_id crowdId from log_device_crowd t 
                    where (t.see_flag =1 or t.setting_flag =1) and t.crowd_id is not null  group by t.crowd_id
                """;
        cur.execute(selectNumSql)
        resultNum = cur.fetchall()
        for result in resultNum:
            crowdNum = result[0];
            crowdId = result[1];
            # 判断 analysis_startup_rate 表是否已经存在当天 开机率数据
            selectCountSql = """
                               select count(1) num from analysis_startup_rate t where 1=1 and t.ref_date =  '%s' and t.crowd_id = %s
                           """ % (date,crowdId)
            cur.execute(selectCountSql)
            resultCount = cur.fetchone()
            countNum = resultCount[0]

            # 计算 当日开机设备量
            selectStartupNumSql = """
                                select count(DISTINCT(t.active_id)) from log_sys_poweron t  where  DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' and 
                t.active_id in(select tt.active_id from log_device_crowd tt where (tt.see_flag =1 or tt.setting_flag =1) and tt.crowd_id = %s and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s')
                            """ % (date,crowdId,date)
            cur.execute(selectStartupNumSql)
            resultStartupNum = cur.fetchone()
            # 计算开机率
            startupRate = 0
            startupNum = resultStartupNum[0]
            pushNum = crowdNum
            if pushNum != 0:
                startupRate = round(startupNum / pushNum, 4)*100

            if countNum == 0:
                print("%s日%s人群新增推送的设备数量为%s,开机设备量为%s" % (date, crowdId,pushNum, startupNum))
                # 插入早间服务可见设备量
                insertSql = """insert into analysis_startup_rate(startup_rate,startup_num,push_num,ref_date,crowd_id) 
                            value(%s,%s,%s,'%s',%s)""" % (startupRate, startupNum, pushNum, date, crowdId)
                cur.execute(insertSql)
            else:
                print("%s日%s人群更新推送的设备数量为%s,开机设备量为%s" % (date,crowdId, pushNum, startupNum))
                # 更新早间服务可见设备量
                updateSql = """
                            update analysis_startup_rate  t set startup_rate = %s ,startup_num = %s,push_num = %s ,update_time = now()  where
                                ref_date = '%s' and t.crowd_id = %s
                        """ % (startupRate, startupNum, pushNum, date,crowdId);
                cur.execute(updateSql)

        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date


if __name__ == '__main__':
    #today = todayYMD()
    date = getdate(1)
    #today = "2021-12-13"
    print ("%s 日期,执行<analysis_startup_rate>报表" % date)
    insertStartupRate(date)
    #insertStartupRateCrowdId(date)

